package cn.smilefox.dao.impl;

import cn.smilefox.dao.CourseDao;
import cn.smilefox.domain.Course;
import cn.smilefox.domain.Teacher;
import cn.smilefox.service.CourseService;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class CourseDaoImpl implements CourseDao {
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    //查询实验课程列表数据
    @Override
    public List<Course> findalltest() {
        List<Course> courseList = jdbcTemplate.query("select * from test_courses", new BeanPropertyRowMapper<Course>(Course.class));
        return courseList;
    }

    //保存课程信息并返回对应id
    @Override
    public Long save(final Course course) {

        //获取保存数据的id
        //创建PreparedStatementCreator
        PreparedStatementCreator creator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                //用原始的jdbc去完成一个PreparedStatement组件
                PreparedStatement preparedStatement = connection.prepareStatement("insert into test_courses values (?,?,?,?,?)",PreparedStatement.RETURN_GENERATED_KEYS);
                preparedStatement.setObject(1,null);
                preparedStatement.setObject(2,course.getCourse_name());
                preparedStatement.setString(3,course.getGrade());
                preparedStatement.setString(4,course.getMajor());
                preparedStatement.setString(5,course.getCourse_num());
                return preparedStatement;
            }
        };

        //创建keyHolder
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();

        //获取主键
        jdbcTemplate.update(creator, keyHolder);
        long id = keyHolder.getKey().longValue();
        return id;
    }

    //查询对于老师的id
    @Override
    public Long findTeacherByName(String teacher) {
        Teacher teacherID = jdbcTemplate.queryForObject("select * from teacher where tc_name=?", new RowMapper<Teacher>() {
            @Override
            public Teacher mapRow(ResultSet resultSet, int i) throws SQLException {
                //获取结果集中的数据
                String id = resultSet.getString("id");
                long Id = Long.valueOf(id);
                // 把数据封装成User对象
                Teacher teacher1 = new Teacher();
                teacher1.setId(Id);
                return teacher1;
            }
        }, teacher);
        Long id = teacherID.getId();

        return id;
    }

    //添加实验课程关系
    @Override
    public void saveTeacherTestRel(Long t_id, Long c_id) {
        jdbcTemplate.update("insert into rela_teacher_test values (?,?)",t_id,c_id);
    }

    //删除实验课关系
    @Override
    public void delTeacherTestRel(Long courseid) {
        jdbcTemplate.update("delete from rela_teacher_test where testc_id=?",courseid);
    }

    //删除实验课数据
    @Override
    public void deltest(Long courseid) {
        jdbcTemplate.update("delete from test_courses where id=?",courseid);

    }

    //查询实验课程
    @Override
    public List<Course> findtest(Course course) {
        List<Course> courseList = jdbcTemplate.query("select * from test_courses where course_name=?", new BeanPropertyRowMapper<Course>(Course.class), course.getCourse_name());
        return courseList;
    }


    //查询理论课所有课程
    @Override
    public List<Course> findalltheory() {
        List<Course> courseList = jdbcTemplate.query("select * from theory_courses", new BeanPropertyRowMapper<Course>(Course.class));
        return courseList;
    }

    //保存理论课程并返回id值
    @Override
    public Long savetheory(final Course course) {
        //获取保存数据的id
        //创建PreparedStatementCreator
        PreparedStatementCreator creator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                //用原始的jdbc去完成一个PreparedStatement组件
                PreparedStatement preparedStatement = connection.prepareStatement("insert into theory_courses values (?,?,?,?,?)",PreparedStatement.RETURN_GENERATED_KEYS);
                preparedStatement.setObject(1,null);
                preparedStatement.setObject(2,course.getCourse_name());
                preparedStatement.setString(3,course.getGrade());
                preparedStatement.setString(4,course.getMajor());
                preparedStatement.setString(5,course.getCourse_num());
                return preparedStatement;
            }
        };

        //创建keyHolder
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();

        //获取主键
        jdbcTemplate.update(creator, keyHolder);
        long id = keyHolder.getKey().longValue();
        return id;
    }

    //根据id值添加理论和教师表的关联数据
    @Override
    public void saveTeacherTheoryRel(Long t_id, Long c_id) {
        jdbcTemplate.update("insert into rela_teacher_theory values (?,?)",t_id,c_id);
    }

    //删除理论关系表数据
    @Override
    public void delTeacherTheoryRel(Long courseid) {
        jdbcTemplate.update("delete from rela_teacher_theory where theoryc_id=?",courseid);
    }

    //删除理论信息表
    @Override
    public void deltheory(Long courseid) {
        jdbcTemplate.update("delete from theory_courses where id=?",courseid);
    }


    //查询理论课表信息
    @Override
    public List<Course> findtheory(Course course) {
        List<Course> courseList = jdbcTemplate.query("select * from theory_courses where course_name=?", new BeanPropertyRowMapper<Course>(Course.class),course.getCourse_name());
        return courseList;
    }

    //通过关系表将特定教师的所具有的所有课程展示出来
    @Override
    public List<Course> findTestById(Long id) {
        List<Course> courseList = jdbcTemplate.query("SELECT * FROM rela_teacher_test tt,teacher t,test_courses tc WHERE tt.teacher_id = t.id AND tc.id = tt.testc_id AND t.id=?;", new BeanPropertyRowMapper<Course>(Course.class), id);
        return courseList;
    }

    @Override
    public List<Course> findTheoryById(Long id) {
        List<Course> courseList = jdbcTemplate.query("SELECT * FROM rela_teacher_theory tt,teacher t,theory_courses tc WHERE tt.teacher_id = t.id AND tc.id = tt.theoryc_id AND t.id=?;", new BeanPropertyRowMapper<Course>(Course.class), id);
        return courseList;
    }

}
